<?php
/**
 * @author by bilee.
 * 时间: 16/12/13 下午7:02
 * 数据分析
 */
namespace Admin\Controller;
use Common\Controller\AdminbaseController;

class DataanalysisController extends AdminbaseController

{
    function index(){


    }

    function user(){


    }
    /**
     * 跑客信息
     */
    function dispatch(){
        $order_model=M("order o");

        $formget = I('formget');
        $order_number=  intval($formget["order_number"]);//订单号
        $order_type=  intval($formget["order_type"]);//订单类型
        $status=  intval($formget["status"]);//订单状态
        $job_number=  $formget["job_number"];//工号
        $dispatch_name=  $formget["dispatch_name"];//配送员姓名
        $start_time=  $formget["start_time"];//开始时间
        $end_time=  $formget["end_time"];//结束时间
        $is_valid=$formget["is_valid"];//是否有效
        $is_reservation=$formget["is_reservation"];//是否预约单


        $formget =array('order_number'=>$order_number
        ,'order_type'=>$order_type,
            'status'=>$status,
            'job_number'=>$job_number,
            'dispatch_name'=>$dispatch_name,
            'is_valid'=>$is_valid,
            'is_reservation'=>$is_reservation,
            'start_time'=>$start_time,
            'end_time'=>$end_time);

        $where = " 1=1 ";

        if(!empty($job_number))
            $where = $where." and d.job_number like '%".$job_number."%'";
        if(!empty($dispatch_name))
            $where = $where." and d.dispatch_name like '%".$dispatch_name."%'";
        if( $order_type!=-1)
            $where = $where.' and o.order_type = '.$order_type;
        if( $status!=-1)
            $where = $where.' and o.status = '.$status;
        if( $is_valid!=-1)
            $where = $where.' and o.is_valid = '.$is_valid;
        if( $is_reservation!=-1)
            $where = $where.' and o.is_reservation = '.$is_reservation;
        if(!empty($start_time))
            $where = $where." and o.order_time >= '".$start_time."  00:00:00'";
        if(!empty($end_time))
            $where = $where." and o.order_time <= '".$end_time." 23:59:59' ";

        $count = $order_model->where($where)->join('left join db_dispatch d on d.dispatch_id = o.dispatch_id ')->count();
        import("Org.Util.Pagemumayi");
        $page = new \Pagemumayi($count,20);
        //分页跳转的时候保证查询条件
        foreach($formget as $key=>$val) {
            $page->parameter .= 'formget['."$key"."]=".urlencode($val)."&";
        }

        $lists = $order_model
            ->field('o.order_id,o.user_id,o.city_id,o.order_number,o.order_time,o.order_type,o.status,o.pay_method,o.order_amount,o.real_amount,d.mobile,d.dispatch_name,d.job_number,o.is_valid,get_vali_code,is_reservation,is_appoint')
            ->join('left join db_dispatch d on d.dispatch_id = o.dispatch_id ')
            ->where($where)
            ->order("o.order_id desc")
            ->limit($page->firstRow . ',' . $page->listRows)
            ->select();

//        dump($order_model->getLastSql());
        //城市
        $city_obj = M("city_code");
        $city_data=$city_obj->field("city_id,city_name")->select();
        $citys=array();
        foreach ($city_data as $u){
            $citys[$u['city_id']]=$u;
        }

        $this->assign('total_count', $count);
        $this->assign("citys",$citys);
        $this->assign("formget",$formget);
        $this->assign('lists', $lists);
        $this->assign("page", $page->show('Admin'));
        $this->display();
    }


    function allpnroutecounts(){

        $this->display();
    }

    /**
     * 异步请求郑州市内跑客信息
     *  order_id 订单id
     */
    function allpnroute(){

        $dispatch_db = M('dispatch');
        $dispatch_obj = $dispatch_db->field('dispatch_id,mobile,longitude,latitude,dispatch_name,place_up_time')->where(array('work_status'=>1,'is_sign'=>2)) ->select();

        if($dispatch_obj){
            echo json_encode($dispatch_obj);
        }

    }

    //订单查看
    public function order_show(){
        $id=  intval(I("get.id"));//
        $users_model=M("order o");//创建与数据库表对应的实例对象
        $data = $users_model->where(array("order_id" => $id))->find();
        if (!$data) {
            $this->error("该订单不存在！");
        }

        //配送员
        $dispatch_obj = M("dispatch");
        $dispatch_data=$dispatch_obj->field("dispatch_id,dispatch_name,mobile,job_number")->select();
        $dispatchs=array();
        foreach ($dispatch_data as $u){
            $dispatchs[$u['dispatch_id']]=$u;
        }
        //城市
        $city_obj = M("city_code");
        $city_data=$city_obj->field("city_id,city_name")->select();
        $citys=array();
        foreach ($city_data as $u){
            $citys[$u['city_id']]=$u;
        }
        //运输工具
        $conv_obj = M("conveyance");
        $conv_data=$conv_obj->field("conveyance_id,conv_name")->select();
        $convs=array();
        foreach ($conv_data as $u){
            $convs[$u['conveyance_id']]=$u;
        }
        //取消事由
        $cancle_obj = M("cancle_reason");
        $cancle_data=$cancle_obj->field("cancle_reason_id,cancle_reason")->select();
        $cancles=array();
        foreach ($cancle_data as $u){
            $cancles[$u['cancle_reason_id']]=$u;
        }
        //优惠券
        $coupon_obj = M("user_coupon");
        $user_coupon_data=$coupon_obj->where(array('user_coupon_id'=>$data['coupon_id']))->field("coupon_id,coupon_type")->find();
        $coupon_obj=M('coupon_info')->where(array('coupon_id'=>$user_coupon_data['coupon_id']))->find();
        $data['deduct_amount']=number_format($coupon_obj['coupon_value'],2);
        $data['coupon_type']=$coupon_obj['coupon_type'];

        $this->assign("dispatchs", $dispatchs);
        $this->assign("citys", $citys);
        $this->assign("convs", $convs);
        $this->assign("cancles", $cancles);
        $this->assign("formget",I('formget'));
        $this->assign("data", $data);//在 Action 类里面使用 assign 方法对模板变量赋值，无论何种变量类型都统一使用 assign 赋值
        $this->display();// 输出模版文件
    }

    /**
     * 路径计算
     * {String} shippingLat 发货纬度
     * String} shippingLng 发货经度
     * {String} collectLat 收货纬度
     * String} collectLng 收货经度
     * order_id 订单id
     */
    function maproute(){
        $ret = M('order')->where(array('order_id'=>I('order_id')))->field('status')->find();
        $shippingLat = (double)I('shippingLat');
        $shippingLng = (double)I('shippingLng');
        $collectLat = (double)I('collectLat');
        $collectLng = (double)I('collectLng');
        $centerLat = ($shippingLat + $collectLat)/2;
        $centerlng = ($shippingLng + $collectLng)/2;
        if($ret){
            $data = array(
                'shippingLat'=>$shippingLat,
                'shippingLng'=>$shippingLng,
                'centerLat'=>$centerLat,
                'centerlng'=>$centerlng,
                'collectLat'=>$collectLat,
                'collectLng'=>$collectLng,
                'order_id'=>I('order_id'),
                'status'=>$ret['status']
            );
            $this->assign('route',$data);
            $this->display();
        }else{
            return RET_NO_V(400,'订单不存在');
        }

    }

    /**
     * 团队接单统计
     */
    function company_rob_orders(){
        $order_db=M('order o ');
        $formget = I('formget');

        $start_time=  $formget["start_time"];//开始时间
        $end_time=  $formget["end_time"];//结束时间

        if(!$start_time)
            $start_time = date('Y-m',time()).'-1';
        if(!$end_time)
            $end_time = date('Y-m-d',time());

        $formget =array('start_time'=>$start_time,
                        'end_time'=>$end_time);

        $where = " o.status= 7 ";

        if(!empty($start_time))
            $where = $where." and o.order_time >= '".$start_time."  00:00:00'";
        if(!empty($end_time))
            $where = $where." and o.order_time <= '".$end_time." 23:59:59' ";

//        $count = $order_db->where($where)->join('db_dispatch d on d.dispatch_id = o.dispatch_id')
//                            ->join('db_company c on d.company_id = c.company_id')
//                            ->where($where)
//                            ->group('d.company_id')->count();
//        import("Org.Util.Pagemumayi");
//        $page = new \Pagemumayi($count,20);
//        //分页跳转的时候保证查询条件
//        foreach($formget as $key=>$val) {
//            $page->parameter .= 'formget['."$key"."]=".urlencode($val)."&";
//        }

        $data = $order_db->field('d.company_id,c.company_name,count(o.order_id) as order_count,sum(o.order_amount) as order_amount')
                    ->join('db_dispatch d on d.dispatch_id = o.dispatch_id')
                    ->join('db_company c on d.company_id = c.company_id')
                    ->where($where)
                    ->group('d.company_id')
                    ->select();
        $this->assign('lists',$data);
        $this->assign("formget",$formget);
        $this->display();
    }
    /**
     * 团队新增关系用户数
     */
    function company_new_users(){
        //一级跑客和用户推荐的用户记录数（包括一级的跑客和用户）
        $recommend_db = M('recommend r1');//推荐人表
        $formget = I('formget');

        $start_time=  $formget["start_time"];//开始时间
        $end_time=  $formget["end_time"];//结束时间

        if(!$start_time)
            $start_time = date('Y-m',time()).'-1';
        if(!$end_time)
            $end_time = date('Y-m-d',time());

        $formget =array('start_time'=>$start_time,
                         'end_time'=>$end_time);

        $where = " ";

        if(!empty($start_time))
            $where = $where." and r1.recommended_time >= '".$start_time." 00:00:00'";
        if(!empty($end_time))
            $where = $where." and r1.recommended_time <= '".$end_time." 23:59:59' ";

        //统计跑客的推荐数量
        $recom_pn = $recommend_db->field('d.company_id,count(r1.recommended_person_id) as one_pn_count')
                                        ->join('db_dispatch d on d.dispatch_id = r1.recom_person_id and r1.recom_person_type=2')
                                        ->where( 'r1.recom_person_type=2 and r1.recommended_person_type=2 '.$where)
                                        ->group(' d.company_id')->select();//获取uid推荐跑客记录数

//        dump('二级推荐用户总数：');
//        dump($recommend_db->getLastSql());
//        dump($recommend_pn_two_three_totalcount);

//        $user_obj['recommend_relation_count'] = $recommend_pn_two_three_totalcount;

        $this->assign('lists',$recom_pn);
        $this->assign("formget",$formget);
        $this->display();
    }

    /**
     * 统计跑客接单的情况
     */
    function dispatch_rob_orders(){
        $dispatch_db=M("dispatch d ");

        $formget = I('formget');
        $mobile=  $formget["mobile"];//配送员姓名
        $dispatch_name=  $formget["dispatch_name"];//配送员姓名
        $start_time=  $formget["start_time"];//开始时间
        $end_time=  $formget["end_time"];//结束时间
        $company_id=$formget["company_id"];//是否有效
        $is_reservation=$formget["is_reservation"];//是否预约单
        if(!$start_time)
            $start_time = date('Y-m-d',time());
        if(!$end_time)
            $end_time = date('Y-m-d',time());

        $formget =array(
            'dispatch_name'=>$dispatch_name,
            'mobile'=>$mobile,
            'company_id'=>$company_id,
            'is_reservation'=>$is_reservation,
            'start_time'=>$start_time,
            'end_time'=>$end_time);

        $where = "  o.`status` =7 ";

        if(!empty($start_time))
            $where = $where." and o.order_time >= '".$start_time."  00:00:00'";
        if(!empty($end_time))
            $where = $where." and o.order_time <= '".$end_time." 23:59:59' ";
        $dis_where=" 1 ";
        if($mobile)
            $dis_where = $dis_where." and d.mobile like '%".$mobile."%'";
        if( $dispatch_name)
            $dis_where = $dis_where." and d.dispatch_name like '%".$dispatch_name."%'";
        if( $company_id && $company_id!=1)
            $dis_where = $dis_where.' and d.company_id = '.$company_id;
        $count = $dispatch_db->join(" (select o.dispatch_id,count(o.order_id) as order_count	,sum(o.order_amount) as order_amount".
                                " from db_order o where ".$where."  GROUP BY o.dispatch_id ".
                                " ) as src  on src.dispatch_id = d.dispatch_id")
                                ->where($dis_where)
                                ->count();
        import("Org.Util.Pagemumayi");
        $page = new \Pagemumayi($count,40);
        //分页跳转的时候保证查询条件
        foreach($formget as $key=>$val) {
            $page->parameter .= 'formget['."$key"."]=".urlencode($val)."&";
        }

        $lists = $dispatch_db
            ->field('d.dispatch_id,d.company_id,d.dispatch_name,d.mobile,src.order_count,src.order_amount')
            ->join(" (select o.dispatch_id,count(o.order_id) as order_count	,sum(o.order_amount) as order_amount,STR_TO_DATE(o.order_time,'%Y-%m-%d') as order_time".
                    " from db_order o where ".$where."  GROUP BY o.dispatch_id".
                    " ) as src  on src.dispatch_id = d.dispatch_id")
            ->where($dis_where)
            ->order(" d.company_id,src.order_count desc")
            ->limit($page->firstRow . ',' . $page->listRows)
            ->select();

        $order_where = $where;
        if($mobile)
            $order_where = $order_where." and d.mobile like '%".$mobile."%'";
        if( $dispatch_name)
            $order_where = $order_where." and d.dispatch_name like '%".$dispatch_name."%'";
        if( $company_id && $company_id!=1)
            $order_where = $order_where.' and d.company_id = '.$company_id;
        $order_amount = M('order o')
            ->field('count(o.order_id) as order_count	,sum(o.order_amount) as order_amount')
            ->join("left join db_dispatch d on d.dispatch_id = o.dispatch_id")
            ->where($order_where)
            ->select();

        $this->assign('company_data',M('company')->select());
        $this->assign('total_count', $count);
        $this->assign('total_order', $order_amount[0]['order_count']);
        $this->assign('total_amount', $order_amount[0]['order_amount']);
        $this->assign("formget",$formget);
        $this->assign('lists', $lists);
        $this->assign("page", $page->show('Admin'));
        $this->display();

    }
}